<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/4/21
 * Time: 11:01
 */

namespace app\common\model;


use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\RichText\Run;
use PhpOffice\PhpSpreadsheet\Writer\Exception;
use think\Model;
use traits\model\SoftDelete;

class BusInquiry extends Model
{
    use SoftDelete;
    protected $table = 'bus_inquiry';
    protected $deleteTime = 'delete_time';
    protected $hidden = ['delete_time'];
    protected $field = true;

    public $quilty = [
        'qulity_service_price'=>'服务站',
        'qulity_org_price'=>'原厂原包',
        'qulity_matching_price'=>'配套',
        'qulity_complete_price'=>'品牌',
        'qulity_suitable_price'=>'适用',
    ];
    public function getBusToCompanyIdsAttr($name){
        return $name?explode(',',$name):'';
    }
    public function getDeadlineDatetimeAttr($name){
        return $name?date('Y-m-d H',strtotime($name)):'';
    }

    public function getFromQuotationSelectionJsonAttr($name)
    {
        return $name?json_decode($name,true):[];
    }

    public function getToQuotationSelectionJsonAttr($name)
    {
        return $name?json_decode($name,true):[];
    }
    public function salesCompany()
    {
        return $this->belongsTo('SalesCompany','bus_from_company_id','id')->bind('province,city,district');
    }

    public function detailInfo()
    {
        return $this->hasMany('BusInquiryDetail','bus_inquiry_id','id')->order('order_no asc,id asc');
    }

    public function detailQuota()
    {
        return $this->hasMany('BusInquiryDetail','bus_inquiry_id','id')->where('state',3)->order('id asc');
    }

    public function detailSales()
    {
        return $this->hasMany('BusInquiryDetail','bus_inquiry_id','id')->where(['state'=>['>',3]])->order('id asc');
    }
    public function allprice()
    {
        return $this->hasMany('BusInquiryDetailSupplier','bus_inquiry_id','id')
            ->field('-1 as id,bus_inquiry_id,add_company_id,bus_inquiry_suppliers_id,sum(qulity_service_price) as qulity_service_price,sum(qulity_org_price) as qulity_org_price,sum(qulity_matching_price) as qulity_matching_price,sum(qulity_complete_price) as qulity_complete_price,sum(qulity_suitable_price) as qulity_suitable_price')
            ->group('bus_inquiry_id,add_company_id')->order('add_company_id');
    }

    public function purchase()
    {
        return $this->hasOne('BusInquirySuppliers','bus_inquiry_id','id')->where('state',6)
            ->field('id,bus_inquiry_id,totall_price as purchase_totall_price,freight as purchase_freight,tax_rate as purchase_tax_rate,returned_money as purchase_returned_money,returned_money_state as purchase_returned_money_state,invoice_state as purchase_invoice_state')
            ->bind('purchase_totall_price,purchase_freight,purchase_tax_rate,purchase_returned_money,purchase_returned_money_state,purchase_invoice_state');
    }

    public function quoteSuppliers()
    {
        return $this->hasMany('BusInquirySuppliers','bus_inquiry_id','id')->field('id,bus_inquiry_id,state')->where('state','in',[2,3,5,6]);
    }

    public function createQuotationFile($data)
    {
        $inquiryModel = new BusInquiry();
        $supplierDetailModel = new BusInquiryDetailSupplier();
        $info = $inquiryModel->with(['detail_quota'])->where(['id'=>$data['id']])->find();
        $eq_nos = $supplierDetailModel->where(['bus_inquiry_id'=>$data['id'],'add_company_id'=>$data['add_company_id']])->column('bus_inquiry_detail_id,oe_no');
        $tax_rate = floatval($info['tax_rate']);
        $file_template = $data['file_template'];
        if(isset($data['with_seal']) && $data['with_seal']){
            $spreadsheet = IOFactory::load('./template/'.$file_template.'/quotation_with_seal.xlsx');
        }else{
            $spreadsheet = IOFactory::load('./template/'.$file_template.'/quotation.xlsx');
        }
        if($file_template === 'ht'){
            $this->htQuotationHandle($info,$tax_rate,$spreadsheet,$eq_nos);
        }elseif($file_template === 'cy'){
            $this->cyQuotationHandle($info,$tax_rate,$spreadsheet,$eq_nos);
        }else{
            $this->personalQuotationHandle($info,$tax_rate,$spreadsheet,$eq_nos);
        }
    }

    /**
     * 鸿天报价单处理
     * @param $info
     * @param $supplierCompanyInfo
     * @param $spreadsheet
     * @return void
     * @throws Exception
     */
    public function htQuotationHandle($info, $tax_rate, $spreadsheet,$eq_nos)
    {
        // 获取当前活动的工作表(默认是第一个工作表)
        $detail_quota = $info['detail_quota'];
        $eq_count = count($detail_quota);
        $sales_company_model = new SalesCompany();
        $sales_info = $sales_company_model->with('parent')->where(['id'=>$info['bus_from_company_id']])->find();
        $worksheet = $spreadsheet->getActiveSheet();
        // A1
        $richText = new RichText();
        $company_name = $sales_info['parent_name']?:$info['bus_from_company_name'];
        // 如果是普通字符串，则需要手动创建富文本元素
        $aText = $company_name.'乘用车报价单';
        // 添加带下划线的文本部分
        $richTextRunafter = new Run();
        $richTextRunafter->getFont()->setName('微软雅黑'); // 设置字体名称
        $richTextRunafter->getFont()->setSize(16);      // 设置字体大小
        $richText->createTextRun($aText)->setFont($richTextRunafter->getFont());
        // 将新的 RichText 对象写回 A1 单元格
        $worksheet->getCell('B3')->setValue($richText);
        //第3行
        $worksheet->getCell('C5')->setValue($info['bus_from_company_name']);
        $worksheet->getCell('F5')->setValue($info['bus_user_name']?:'');
        $worksheet->getCell('I5')->setValue($info['bus_user_phone']?:'');
        //第4行
        $worksheet->getCell('C6')->setValue($info['brand_name']?:'');
        $worksheet->getCell('F6')->setValue($info['car_model']?:'');
        $worksheet->getCell('I6')->setValue($info['vehicle_license_num']?:'');
        //第5行
        $worksheet->getCell('C7')->setValue($info['repair_company_name']?:'');
        $worksheet->getCell('F7')->setValue($info['repair_company_phone']?:'');
        $worksheet->getCell('I7')->setValue(date('Y-m-d'));
        //第6行
        /*$remark = '该报价含运费含13%专票';
        $worksheet->getCell('B6')->setValue($remark);*/

        $startNum = 20;
        $startRow = 29;
        $numberOfRowsToInsert = $eq_count>20?$eq_count - 20:0;

        if($eq_count>20){
            // 插入行
            for ($i = $numberOfRowsToInsert; $i >= 1; $i--) {
                $worksheet->insertNewRowBefore($startRow + 1, 1);
            }
            // 填充数据
            for ($row = $startRow; $row <= $startRow + $numberOfRowsToInsert; $row++) {
                $worksheet->setCellValue('B' . $row, $startNum+($row - $startRow));
            }
            $worksheet->setCellValue('D' . ($startRow+$numberOfRowsToInsert+1), "=sum(D10:D". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('E' . ($startRow+$numberOfRowsToInsert+1), "=sum(E10:E". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('F' . ($startRow+$numberOfRowsToInsert+1), "=sum(F10:F". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('G' . ($startRow+$numberOfRowsToInsert+1), "=sum(G10:G". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('H' . ($startRow+$numberOfRowsToInsert+1), "=sum(H10:H". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('I' . ($startRow+$numberOfRowsToInsert+1), "=sum(I10:I". ($startRow+$numberOfRowsToInsert).')');
        }

        for ($i = 0;$i < $eq_count;$i++) {
            $row = $i+10;
            $oe_no = isset($eq_nos[$detail_quota[$i]['id']])?$eq_nos[$detail_quota[$i]['id']]:'';
            $service_price = in_array('service',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_service_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $org_price = in_array('org',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_org_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $matching_price = in_array('matching',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_matching_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $complete_price = in_array('complete',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_complete_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $suitable_price = in_array('suitable',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_suitable_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $worksheet->setCellValue('C' . $row, $detail_quota[$i]['eq_name']);
            $worksheet->setCellValue('D' . $row, $detail_quota[$i]['num']);
            $worksheet->setCellValue('E' . $row, $service_price);
            $worksheet->setCellValue('F' . $row, $org_price);
            $worksheet->setCellValue('G' . $row, $matching_price);
            $worksheet->setCellValue('H' . $row,$complete_price);
            $worksheet->setCellValue('I' . $row, $suitable_price);
            $worksheet->setCellValue('J' . $row, $oe_no);
        }
        $num = $worksheet->getHighestRow();
        for ($i = 0;$i < $num;$i++) {
            $worksheet->getRowDimension($i)->setRowHeight(25);
        }
        // 保存Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $files = $info['vehicle_license_num'].'报价单'.date('YmdHis').'.xlsx';
//        $writer->save('./static/fileds/'.$files);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename='.$files);
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }

    /**
     * 车友模版报价单处理
     * @param $info
     * @param $supplierCompanyInfo
     * @param $spreadsheet
     * @return void
     * @throws Exception
     */
    public function cyQuotationHandle($info, $tax_rate, $spreadsheet,$eq_nos)
    {
        // 获取当前活动的工作表(默认是第一个工作表)
        $detail_quota = $info['detail_quota'];
        $eq_count = count($detail_quota);

        $worksheet = $spreadsheet->getActiveSheet();
        // B
        $worksheet->getCell('B5')->setValue('机构名称：'.$info['bus_from_company_name']);
        $worksheet->getCell('B6')->setValue('车牌号：'.$info['vehicle_license_num']?:'');
        $worksheet->getCell('B7')->setValue('品牌：'.$info['brand_name']?:'');
        $worksheet->getCell('B8')->setValue('车型：'.$info['car_model']?:'');
        // E
        $worksheet->getCell('E5')->setValue('询价人姓名：'.$info['bus_user_name']?:'');
        $worksheet->getCell('E6')->setValue('询价人电话：'.$info['bus_user_phone']?:'');
        $worksheet->getCell('E7')->setValue('修理厂名称：'.$info['repair_company_name']?:'');
        $worksheet->getCell('E8')->setValue('修理厂电话：'.$info['repair_company_phone']?:'');

        $worksheet->getCell('G4')->setValue('订单日期：'.date('Y.m.d'));

        $startNum = 20;
        $startRow = 29;
        $numberOfRowsToInsert = $eq_count>20?$eq_count - 20:0;
        if($eq_count>20){
            // 插入行
            for ($i = $numberOfRowsToInsert; $i >= 1; $i--) {
                $worksheet->insertNewRowBefore($startRow + 1, 1);
            }
            // 填充数据
            for ($row = $startRow; $row <= $startRow + $numberOfRowsToInsert; $row++) {
                $worksheet->setCellValue('B' . $row, $startNum+($row - $startRow));
            }
            $worksheet->setCellValue('E' . ($startRow+$numberOfRowsToInsert+1), "=sum(E11:C". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('F' . ($startRow+$numberOfRowsToInsert+1), "=sum(F11:D". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('G' . ($startRow+$numberOfRowsToInsert+1), "=sum(G11:E". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('H' . ($startRow+$numberOfRowsToInsert+1), "=sum(H11:F". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('I' . ($startRow+$numberOfRowsToInsert+1), "=sum(I118:G". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('J' . ($startRow+$numberOfRowsToInsert+1), "=sum(J118:G". ($startRow+$numberOfRowsToInsert).')');
        }

        for ($i = 0;$i < $eq_count;$i++) {
            $row = $i+10;
            $oe_no = isset($eq_nos[$detail_quota[$i]['id']])?$eq_nos[$detail_quota[$i]['id']]:'';
            $service_price = in_array('service',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_service_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $org_price = in_array('org',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_org_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $matching_price = in_array('matching',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_matching_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $complete_price = in_array('complete',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_complete_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $suitable_price = in_array('suitable',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_suitable_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $worksheet->setCellValue('C' . $row, $oe_no);
            $worksheet->setCellValue('D' . $row, $detail_quota[$i]['eq_name']);
            $worksheet->setCellValue('E' . $row, $detail_quota[$i]['num']);
            $worksheet->setCellValue('F' . $row, $service_price);
            $worksheet->setCellValue('G' . $row, $org_price);
            $worksheet->setCellValue('H' . $row, $matching_price);
            $worksheet->setCellValue('I' . $row, $complete_price);
            $worksheet->setCellValue('J' . $row,$suitable_price);
            // $worksheet->setCellValue('K' . $row, $detail_quota[$i]['common']);
        }
        $num = $worksheet->getHighestRow();
        for ($i = 0;$i < $num;$i++) {
            $worksheet->getRowDimension($i)->setRowHeight(25);
        }
        // 保存Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $files = $info['vehicle_license_num'].'报价单'.date('YmdHis').'.xlsx';
//        $writer->save('./static/fileds/'.$files); exit;
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename='.$files);
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }

    /**
     * 个体户报价模版处理
     * @param $info
     * @param $supplierCompanyInfo
     * @param $spreadsheet
     * @return void
     * @throws Exception
     */
    public function personalQuotationHandle($info, $tax_rate, $spreadsheet,$eq_nos)
    {
        // 获取当前活动的工作表(默认是第一个工作表)
        $detail_quota = $info['detail_quota'];
        $eq_count = count($detail_quota);

        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->getRowDimension(2)->setRowHeight(35);
        // B2
        $worksheet->setCellValue('L3',date('Y.m.d'));
        //第4行
        $worksheet->setCellValue('C4',$info['bus_from_company_name']);
        $worksheet->setCellValue('F4',$info['vehicle_license_num']?:'');
        $worksheet->setCellValue('I4',$info['brand_name']?:'');
        $worksheet->setCellValue('L4',$info['car_model']?:'');
        //第5行
        $worksheet->getCell('C5')->setValue($info['repair_company_name']?:'');
        $worksheet->getCell('F5')->setValue($info['repair_company_phone']?:'');
        $worksheet->getCell('I5')->setValue($info['bus_user_name']?:'');
        $worksheet->getCell('L5')->setValue($info['bus_user_phone']?:'');
        //第6行
        /*$remark = '该报价含运费含1%专票';
        $remark = $info['common']?$info['common'].'，'.$remark:$remark;
        $worksheet->getCell('C6')->setValue($remark);*/

        $startNum = 20;
        $startRow = 27;
        $numberOfRowsToInsert = $eq_count>20?$eq_count - 20:0;
        if($eq_count>20){
            // 插入行
            for ($i = $numberOfRowsToInsert; $i >= 1; $i--) {
                $worksheet->insertNewRowBefore($startRow + 1, 1);
            }
            // 填充数据
            for ($row = $startRow; $row <= $startRow + $numberOfRowsToInsert; $row++) {
                $worksheet->setCellValue('B' . $row, $startNum+($row - $startRow));
                $worksheet->mergeCells('C'.$row.':D'.$row);
                $worksheet->mergeCells('K'.$row.':M'.$row);
            }
            $worksheet->setCellValue('E' . ($startRow+$numberOfRowsToInsert+1), "=sum(E8:E". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('F' . ($startRow+$numberOfRowsToInsert+1), "=sum(F8:F". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('G' . ($startRow+$numberOfRowsToInsert+1), "=sum(G8:G". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('H' . ($startRow+$numberOfRowsToInsert+1), "=sum(H8:G". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('I' . ($startRow+$numberOfRowsToInsert+1), "=sum(I8:G". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('J' . ($startRow+$numberOfRowsToInsert+1), "=sum(J8:G". ($startRow+$numberOfRowsToInsert).')');
        }

        for ($i = 0;$i < $eq_count;$i++) {
            $row = $i+8;
            $service_price = in_array('service',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_service_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $org_price = in_array('org',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_org_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $matching_price = in_array('matching',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_matching_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $complete_price = in_array('complete',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_complete_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $suitable_price = in_array('suitable',$detail_quota[$i]['quote_quilty'])?round($detail_quota[$i]['qulity_suitable_price']*(100+$tax_rate)/100*$detail_quota[$i]['num']):'';
            $eq_name = $detail_quota[$i]['eq_name'];
            $oe_no = isset($eq_nos[$detail_quota[$i]['id']])?$eq_nos[$detail_quota[$i]['id']]:'';
            if($oe_no){
                $eq_name = $detail_quota[$i]['eq_name'] . '(OE码：'.$oe_no.')';
            }
            $worksheet->setCellValue('C' . $row, $eq_name);
            $worksheet->setCellValue('E' . $row, $detail_quota[$i]['num']);
            $worksheet->setCellValue('F' . $row, $service_price);
            $worksheet->setCellValue('G' . $row,$org_price);
            $worksheet->setCellValue('H' . $row, $matching_price);
            $worksheet->setCellValue('I' . $row, $complete_price);
            $worksheet->setCellValue('J' . $row, $suitable_price);
            // $worksheet->setCellValue('K' . $row, $detail_quota[$i]['common']);
        }
        $num = $worksheet->getHighestRow();
        for ($i = 3;$i < $num;$i++) {
            $worksheet->getRowDimension($i)->setRowHeight(25);
        }
        // 保存Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $files = $info['vehicle_license_num'].'报价单'.date('YmdHis').'.xlsx';
        // $writer->save('./static/fileds/'.$files);exit;
        //获取文件内容
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition:attachment;filename=' . $files);//声明作为附件处理和下载后文件的名称
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }


    public function createSalesPurchaseFile($data)
    {
        $type = $data['type'];
        $file_template = $data['file_template'];
        if($type === 'sales'){
            $inquiryModel = new BusInquiry();
            $info = $inquiryModel->where(['id'=>$data['id']])->find();
            $detailModel = new BusInquiryDetail();
            $info['detail_info'] = $detailModel->where(['bus_inquiry_id'=>$data['id'],'state'=>5])->whereNotNull('sale_use_quilty')->select();
            $tax_rate = floatval($info['tax_rate']);
            if(isset($data['with_seal']) && $data['with_seal']){
                $spreadsheet = IOFactory::load('./template/'.$file_template.'/sales_with_seal.xlsx');
            }else{
                $spreadsheet = IOFactory::load('./template/'.$file_template.'/sales.xlsx');
            }
        }else{
            $inquiryModel = new BusInquirySuppliers();
            $info = $inquiryModel->where(['id'=>$data['id']])->find();
            $tax_rate = floatval($info['tax_rate']);
            $detailModel = new BusInquiryDetailSupplier();
            $info['detail_info'] = $detailModel->where(['bus_inquiry_suppliers_id'=>$data['id'],'state'=>5])->whereNotNull('make_sure_quilty')->select();
            if(isset($data['with_seal']) && $data['with_seal']){
                $spreadsheet = IOFactory::load('./template/'.$file_template.'/purchase_with_seal.xlsx');
            }else{
                $spreadsheet = IOFactory::load('./template/'.$file_template.'/purchase.xlsx');
            }
        }

        if($file_template === 'ht'){
            $this->htSalesPurchaseHandle($info,$tax_rate,$spreadsheet,$type);
        }elseif($file_template === 'cy'){
            $this->cySalesPurchaseHandle($info,$tax_rate,$spreadsheet,$type);
        }else{
            $this->personalSalesPurchaseHandle($info,$tax_rate,$spreadsheet,$type);
        }
    }

    /**
     * 鸿天报价单处理
     * @param $info
     * @param $supplierCompanyInfo
     * @param $spreadsheet
     * @return void
     * @throws Exception
     */
    public function htSalesPurchaseHandle($info, $tax_rate, $spreadsheet,$type)
    {
        // 获取当前活动的工作表(默认是第一个工作表)
        $detail = $info['detail_info'];
        $addnum = 0;
        if(floatval($info['freight']) >0){
            $addnum += 1;
        }
        if(floatval($tax_rate) >0 && round($info['totall_price'] + $info['freight']) > 0) {
            $addnum += 1;
        }
        $eq_count = count($detail)+$addnum;

        $worksheet = $spreadsheet->getActiveSheet();
        // B3
        $sales_company_model = new SalesCompany();
        $sales_info = $sales_company_model->with('parent')->where(['id'=>$info['bus_from_company_id']])->find();
        $richText = new RichText();
        $company_name = $sales_info['parent_name']?:$info['bus_from_company_name'];
        // 如果是普通字符串，则需要手动创建富文本元素
        $textAfter = $type=='sales'?'乘用车供货凭证':'乘用车采购凭证';
        $bText = $company_name.$textAfter;
        // 添加带下划线的文本部分
        $richTextRunafter = new Run();
        $richTextRunafter->getFont()->setName('微软雅黑'); // 设置字体名称
        $richTextRunafter->getFont()->setSize(16);      // 设置字体大小
        $richText->createTextRun($bText)->setFont($richTextRunafter->getFont());
        // 将新的 RichText 对象写回 A1 单元格
        $worksheet->getCell('B3')->setValue($richText);
        //第5行
        $worksheet->getCell('C5')->setValue($info['bus_from_company_name']);
        $worksheet->getCell('E5')->setValue($info['bus_user_name']?:'');
        $worksheet->getCell('H5')->setValue($info['bus_user_phone']?:'');
        //第6行
        $worksheet->getCell('C6')->setValue($info['brand_name']?:'');
        $worksheet->getCell('E6')->setValue($info['car_model']?:'');
        $worksheet->getCell('H6')->setValue($info['vehicle_license_num']?:'');
        //第7行
        $worksheet->getCell('C7')->setValue($info['repair_company_name']?:'');
        $worksheet->getCell('E7')->setValue($info['repair_company_phone']?:'');
        $worksheet->getCell('H7')->setValue(date('Y.m.d',strtotime($info['sure_datetime'])));
        //第8行
        // $worksheet->getCell('C8')->setValue($info['common']?:'');

        $startNum = 20;
        $startRow = 29;
        $numberOfRowsToInsert = $eq_count>20?$eq_count - 20:0;

        if($eq_count>20){
            // 插入行
            for ($i = $numberOfRowsToInsert; $i >= 1; $i--) {
                $worksheet->insertNewRowBefore($startRow + 1, 1);
            }
            // 填充数据
            for ($row = $startRow; $row <= $startRow + $numberOfRowsToInsert; $row++) {
                $worksheet->setCellValue('B' . $row, $startNum+($row - $startRow));
                $worksheet->mergeCells('C'.$row.':D'.$row);
            }
            $worksheet->setCellValue('E' . ($startRow+$numberOfRowsToInsert+1), "=sum(E10:E". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('G' . ($startRow+$numberOfRowsToInsert+1), "=sum(G10:G". ($startRow+$numberOfRowsToInsert).')');
        }
        $use_quilty = $type=='sales'?'sale_use_quilty':'make_sure_quilty';
        for ($i = 0;$i < $eq_count-$addnum;$i++) {
            $row = $i+10;
            $worksheet->setCellValue('C' . $row, $detail[$i]['eq_name']);
            $worksheet->setCellValue('E' . $row, $detail[$i]['num']);
            $worksheet->setCellValue('F' . $row, $detail[$i][$detail[$i][$use_quilty]]);
            $worksheet->setCellValue('G' . $row, $detail[$i]['totall_price']);
            $worksheet->setCellValue('H' . $row, $this->quilty[$detail[$i][$use_quilty]]);
            $worksheet->setCellValue('I' . $row, $detail[$i]['oe_no']);
        }
        $next_num = $eq_count - $addnum + 10;
        if(floatval($info['freight']) >0) {
            $worksheet->setCellValue('C' . $next_num, '运费');
            $worksheet->setCellValue('E' . $next_num, 1);
            $worksheet->setCellValue('F' . $next_num, round($info['freight']));
            $worksheet->setCellValue('G' . $next_num, round($info['freight']));
            $worksheet->setCellValue('I' . $next_num, '含木架费');
            $next_num += 1;
        }
        if(floatval($tax_rate) >0 && round($info['totall_price'] + $info['freight']) > 0) {
            $worksheet->setCellValue('C' . $next_num, '税金');
            $worksheet->setCellValue('E' . $next_num, 1);
            $worksheet->setCellValue('F' . $next_num, round(($info['totall_price'] + $info['freight']) * $tax_rate / 100));
            $worksheet->setCellValue('G' . $next_num, round(($info['totall_price'] + $info['freight']) * $tax_rate / 100));
            $worksheet->setCellValue('I' . $next_num, '13%专票');
        }
        $num = $worksheet->getHighestRow();
        for ($i = 0;$i < $num;$i++) {
            $worksheet->getRowDimension($i)->setRowHeight(25);
        }

        // 保存Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $file_name = $type=='sales'?'直供凭证':'采购凭证';
        $files = $info['vehicle_license_num'].$file_name.date('YmdHis').'.xlsx';
//        $writer->save('./static/fileds/'.$files);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename='.$files);
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }

    /**
     * 车友模版报价单处理
     * @param $info
     * @param $supplierCompanyInfo
     * @param $spreadsheet
     * @return void
     * @throws Exception
     */
    public function cySalesPurchaseHandle($info, $tax_rate, $spreadsheet,$type)
    {
        // 获取当前活动的工作表(默认是第一个工作表)
        $detail = $info['detail_info'];
        $addnum = 0;
        if(floatval($info['freight']) >0){
            $addnum += 1;
        }
        if(floatval($tax_rate) >0 && round($info['totall_price'] + $info['freight']) > 0) {
            $addnum += 1;
        }
        $eq_count = count($detail)+$addnum;

        $worksheet = $spreadsheet->getActiveSheet();
        // B
        $worksheet->getCell('B5')->setValue('机构名称：'.$info['bus_from_company_name']);
        $worksheet->getCell('B6')->setValue('车牌号：'.$info['vehicle_license_num']?:'');
        $worksheet->getCell('B7')->setValue('品牌：'.$info['brand_name']?:'');
        $worksheet->getCell('B8')->setValue('车型：'.$info['car_model']?:'');
        // E
        $worksheet->getCell('E5')->setValue('询价人姓名：'.$info['bus_user_name']?:'');
        $worksheet->getCell('E6')->setValue('询价人电话：'.$info['bus_user_phone']?:'');
        $worksheet->getCell('E7')->setValue('修理厂名称：'.$info['repair_company_name']?:'');
        $worksheet->getCell('E8')->setValue('修理厂电话：'.$info['repair_company_phone']?:'');

        $worksheet->getCell('G4')->setValue('订单日期：'.date('Y.m.d',strtotime($info['sure_datetime'])));

        $startNum = 20;
        $startRow = 29;
        $numberOfRowsToInsert = $eq_count>20?$eq_count-20:0;
        if($eq_count>20){
            // 插入行
            for ($i = $numberOfRowsToInsert; $i >= 1; $i--) {
                $worksheet->insertNewRowBefore($startRow + 1, 1);
            }
            // 填充数据
            for ($row = $startRow; $row <= $startRow + $numberOfRowsToInsert; $row++) {
                $worksheet->setCellValue('B' . $row, $startNum+($row - $startRow));
            }
            $worksheet->setCellValue('E' . ($startRow+$numberOfRowsToInsert+1), "=sum(E11:C". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('G' . ($startRow+$numberOfRowsToInsert+1), "=sum(G11:E". ($startRow+$numberOfRowsToInsert).')');
        }

        $use_quilty = $type=='sales'?'sale_use_quilty':'make_sure_quilty';
        for ($i = 0;$i < $eq_count-$addnum;$i++) {
            $row = $i+10;
            $worksheet->setCellValue('C' . $row, $detail[$i]['oe_no']);
            $worksheet->setCellValue('D' . $row, $detail[$i]['eq_name']);
            $worksheet->setCellValue('E' . $row, $detail[$i]['num']);
            $worksheet->setCellValue('F' . $row, $detail[$i][$detail[$i][$use_quilty]]);
            $worksheet->setCellValue('G' . $row, $detail[$i]['totall_price']);
            $worksheet->setCellValue('H' . $row, $this->quilty[$detail[$i][$use_quilty]]);
            // $worksheet->setCellValue('I' . $row, $detail[$i]['common']);
        }
        $next_num = $eq_count - $addnum + 10;
        if(floatval($info['freight']) >0) {
            $worksheet->setCellValue('D' . $next_num, '运费');
            $worksheet->setCellValue('E' . $next_num, 1);
            $worksheet->setCellValue('F' . $next_num, round($info['freight']));
            $worksheet->setCellValue('G' . $next_num, round($info['freight']));
            $worksheet->setCellValue('I' . $next_num, '含木架费');
            $next_num += 1;
        }
        if(floatval($tax_rate) >0 && round($info['totall_price'] + $info['freight']) > 0) {
            $worksheet->setCellValue('D' . $next_num, '税金');
            $worksheet->setCellValue('E' . $next_num, 1);
            $worksheet->setCellValue('F' . $next_num, round(($info['totall_price'] + $info['freight']) * $tax_rate / 100));
            $worksheet->setCellValue('G' . $next_num, round(($info['totall_price'] + $info['freight']) * $tax_rate / 100));
            $worksheet->setCellValue('I' . $next_num, '13%专票');
        }
        $num = $worksheet->getHighestRow();
        for ($i = 0;$i < $num;$i++) {
            $worksheet->getRowDimension($i)->setRowHeight(25);
        }
        // 保存Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $file_name = $type=='sales'?'直供凭证':'采购凭证';
        $files = $info['vehicle_license_num'].$file_name.date('YmdHis').'.xlsx';
//        $writer->save('./static/fileds/'.$files); exit;
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename='.$files);
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }

    /**
     * 个体户报价模版处理
     * @param $info
     * @param $supplierCompanyInfo
     * @param $spreadsheet
     * @return void
     * @throws Exception
     */
    public function personalSalesPurchaseHandle($info, $tax_rate, $spreadsheet,$type)
    {
        // 获取当前活动的工作表(默认是第一个工作表)
        $detail = $info['detail_info'];
        $addnum = 0;
        if(floatval($info['freight']) >0){
            $addnum += 1;
        }
        if(floatval($tax_rate) >0 && round($info['totall_price'] + $info['freight']) > 0) {
            $addnum += 1;
        }
        $eq_count = count($detail)+$addnum;

        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->getRowDimension(2)->setRowHeight(35);
        // B2
        $file_name = $type=='sales'?'供货单':'采购单';
        $worksheet->setCellValue('L3',date('Y.m.d',strtotime($info['sure_datetime'])));
        //第4行
        $worksheet->setCellValue('C4',$info['bus_from_company_name']);
        $worksheet->setCellValue('F4',$info['vehicle_license_num']?:'');
        $worksheet->setCellValue('I4',$info['brand_name']?:'');
        $worksheet->setCellValue('L4',$info['car_model']?:'');
        //第5行
        $worksheet->getCell('C5')->setValue($info['repair_company_name']?:'');
        $worksheet->getCell('F5')->setValue($info['repair_company_phone']?:'');
        $worksheet->getCell('I5')->setValue($info['bus_user_name']?:'');
        $worksheet->getCell('L5')->setValue($info['bus_user_phone']?:'');
        //第6行
//        $worksheet->getCell('C6')->setValue($info['common']?:'');

        $startNum = 20;
        $startRow = 27;
        $numberOfRowsToInsert = $eq_count>20?$eq_count - 20:0;
        if($eq_count>20){
            // 插入行
            for ($i = $numberOfRowsToInsert; $i >= 1; $i--) {
                $worksheet->insertNewRowBefore($startRow + 1, 1);
            }
            // 填充数据
            for ($row = $startRow; $row <= $startRow + $numberOfRowsToInsert; $row++) {
                $worksheet->setCellValue('B' . $row, $startNum+($row - $startRow));
                $worksheet->mergeCells('C'.$row.':D'.$row);
                $worksheet->mergeCells('J'.$row.':M'.$row);
            }
            $worksheet->setCellValue('F' . ($startRow+$numberOfRowsToInsert+1), "=sum(F8:F". ($startRow+$numberOfRowsToInsert).')');
            $worksheet->setCellValue('H' . ($startRow+$numberOfRowsToInsert+1), "=sum(H8:G". ($startRow+$numberOfRowsToInsert).')');
        }
        $use_quilty = $type=='sales'?'sale_use_quilty':'make_sure_quilty';
        for ($i = 0;$i < $eq_count-$addnum;$i++) {
            $row = $i+8;
            $worksheet->setCellValue('C' . $row, $detail[$i]['eq_name']);
            $worksheet->setCellValue('E' . $row, $detail[$i]['oe_no']);
            $worksheet->setCellValue('F' . $row, $detail[$i]['num']);
            $worksheet->setCellValue('G' . $row, $detail[$i][$detail[$i][$use_quilty]]);
            $worksheet->setCellValue('H' . $row, $detail[$i]['totall_price']);
            $worksheet->setCellValue('I' . $row, $this->quilty[$detail[$i][$use_quilty]]);
            // $worksheet->setCellValue('J' . $row, $detail[$i]['common']);
        }

        $next_num = $eq_count - $addnum + 8;
        if(floatval($info['freight']) >0){
            $worksheet->setCellValue('C' . $next_num, '运费');
            $worksheet->setCellValue('F' . $next_num, 1);
            $worksheet->setCellValue('G' . $next_num, round($info['freight']));
            $worksheet->setCellValue('H' . $next_num, round($info['freight']));
            $worksheet->setCellValue('J' . $next_num,'含木架费');
            $next_num += 1;
        }

        if(floatval($tax_rate) > 0 && round($info['totall_price'] + $info['freight']) > 0){
            $worksheet->setCellValue('C' . $next_num, '税金');
            $worksheet->setCellValue('F' . $next_num, 1);
            $worksheet->setCellValue('G' . $next_num, round(($info['totall_price']+$info['freight'])*$tax_rate/100));
            $worksheet->setCellValue('H' . $next_num, round(($info['totall_price']+$info['freight'])*$tax_rate/100));
            $worksheet->setCellValue('J' . $next_num,'1%票');
        }
        $num = $worksheet->getHighestRow();
        for ($i = 3;$i < $num;$i++) {
            $worksheet->getRowDimension($i)->setRowHeight(25);
        }
        // 保存Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $files = $info['vehicle_license_num'].$file_name.date('YmdHis').'.xlsx';
        // $writer->save('./static/fileds/'.$files);exit;
        //获取文件内容
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition:attachment;filename=' . $files);//声明作为附件处理和下载后文件的名称
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }

    public function orderHandle($info)
    {
        $spreadsheet = IOFactory::load('./template/Ledger.xlsx');
        $worksheet = $spreadsheet->getActiveSheet();

        $num = count($info);
        $startRow = 8;
        $numberOfRowsToInsert = $num>4 ? $num - 4:0;

        if($numberOfRowsToInsert){
            // 插入行
            for ($i = $numberOfRowsToInsert; $i >= 1; $i--) {
                $worksheet->insertNewRowBefore($startRow + 1, 1);
            }
        }

        // 获取当前活动的工作表(默认是第一个工作表)
        foreach ($info as $key=>$item){
            $item['tax'] = round(($item['freight'] + $item['totall_price'])*$item['tax_rate']/100);
            $item['totall_price_tax'] = round($item['freight'] + $item['totall_price']+$item['tax']);
            $item['returned_diff'] = round($item['totall_price_tax'] - $item['returned_money']);
            $item['total_cost'] = round($item['totall_price_tax'] + $item['repair_company_money'] + $item['loss_assessor_money']);
            $item['sales_totall_price_tax'] = round(($item['sales_freight'] + $item['sales_totall_price'])*$item['sales_tax_rate']/100);
            $item['sales_returned_diff'] = round($item['sales_totall_price_tax'] - $item['sales_returned_money']);
            $item['profit'] = round($item['sales_returned_money'] - $item['total_cost']);
            $item['profit_rate'] = $item['sales_returned_money']>0?sprintf('%.2f',$item['profit'] / $item['sales_returned_money'] * 100).'%':0;
            $item['plan_profit_rate'] = $item['sales_totall_price_tax']>0?sprintf('%.2f',($item['sales_totall_price_tax']-$item['total_cost']) / $item['sales_totall_price_tax'] * 100).'%':0;
            $item['is_pay_purchase'] = '否';
            if($item['returned_money_state'] == 2){
                $item['is_pay_purchase'] = '是(含税点)';
            }elseif($item['returned_money_state'] == 1){
                $item['is_pay_purchase'] = '是(含税点) 已付：'.$item['returned_money'];
            }
            $item['is_pay_sales'] = $item['sales_returned_money_state']==2?'是':'否';
            $business_entity_name = '';
            if($item['business_entity']=='ht'){
                $business_entity_name = '鸿天';
            }elseif($item['business_entity']=='cy'){
                $business_entity_name = '车友';
            }elseif($item['business_entity']=='personal'){
                $business_entity_name = '个体户';
            }

            $worksheet->setCellValue('A' . ($key+5), $key+1);
            $worksheet->setCellValue('B' . ($key+5), $item['sure_datetime']);
            $worksheet->setCellValue('C' . ($key+5), $business_entity_name);
            $worksheet->setCellValue('D' . ($key+5), $item['add_user_name']);
            $worksheet->setCellValue('E' . ($key+5), $item['bus_from_company_name']);
            $worksheet->setCellValue('F' . ($key+5), $item['city']);
            $worksheet->setCellValue('G' . ($key+5), $item['vehicle_license_num']);
            $worksheet->setCellValue('H' . ($key+5), $item['car_model']);
            $worksheet->setCellValue('I' . ($key+5), $item['bus_user_name']);
            $worksheet->setCellValue('J' . ($key+5), $item['bus_user_phone']);
            $worksheet->setCellValue('K' . ($key+5), $item['repair_company_name']);
            $worksheet->setCellValue('L' . ($key+5), $item['repair_company_phone']);
            $worksheet->setCellValue('M' . ($key+5), $item['bus_to_company_name']);
            $worksheet->setCellValue('N' . ($key+5), $item['totall_price']);
            $worksheet->setCellValue('O' . ($key+5), $item['freight']);
            $worksheet->setCellValue('P' . ($key+5), $item['tax']);
            $worksheet->setCellValue('Q' . ($key+5), $item['totall_price_tax']);
            $worksheet->setCellValue('R' . ($key+5), $item['loss_assessor_money']);
            $worksheet->setCellValue('S' . ($key+5), $item['loss_assessor_type']);
            $worksheet->setCellValue('T' . ($key+5), $item['loss_assessor_datetime']);
            $worksheet->setCellValue('U' . ($key+5), $item['repair_company_money']);
            $worksheet->setCellValue('V' . ($key+5), $item['repair_company_type']);
            $worksheet->setCellValue('W' . ($key+5), $item['repair_company_datetime']);
            $worksheet->setCellValue('X' . ($key+5), $item['total_cost']);
            $worksheet->setCellValue('Y' . ($key+5), $item['is_pay_purchase']);
            $worksheet->setCellValue('Z' . ($key+5), $item['returned_money_datetime']);
            $worksheet->setCellValue('AA' . ($key+5), $item['sales_totall_price_tax']);
            $worksheet->setCellValue('AB' . ($key+5), $item['sales_returned_money']);
            $worksheet->setCellValue('AC' . ($key+5), $item['sales_returned_money_datetime']);
            $worksheet->setCellValue('AD' . ($key+5), $item['sales_returned_diff']);
            $worksheet->setCellValue('AE' . ($key+5), $item['sales_returned_money_type']);
            $worksheet->setCellValue('AF' . ($key+5), $item['is_pay_sales']);
            $worksheet->setCellValue('AG' . ($key+5), $item['profit']);
            $worksheet->setCellValue('AH' . ($key+5), $item['profit_rate']);
            $worksheet->setCellValue('AI' . ($key+5), $item['plan_profit_rate']);
            $worksheet->setCellValue('AJ' . ($key+5), $item['invoice_state']==2?$item['invoice_datetime'].'已开':'未开');
            $worksheet->setCellValue('AK' . ($key+5), $item['sales_invoice_state']==2?$item['sales_invoice_datetime'].'已开':'未开');
            $worksheet->setCellValue('AL' . ($key+5), $item['have_returns']?'有':'无');
        }
        // 保存Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $files = '财务对账'.date('YmdHis').'.xlsx';
        // $writer->save('./static/fileds/'.$files);exit;
        //获取文件内容
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition:attachment;filename=' . $files);//声明作为附件处理和下载后文件的名称
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit;
    }
}